Re: [SQL] Letting a function work on NULL

At 21:53 +0300 on 20/06/1999, Anton de Wet wrote:


> But all gives:
>
> select ym(NULL);
> ERROR:  typeidTypeRelid: Invalid type - oid = 0
>
> Is there any way to specify an exception on the input type?

There are two different issues here. First, passing the "literal" NULL to
functions always results in this. In SQL92, NULL is not even considered a
literal. Try this, for example:

testing=> select abstime( NULL ) from test2;
ERROR:  typeidTypeRelid: Invalid type - oid = 0

(abstime() is a builting function converting to abstime);

In order to test the functionality of a function in nulls, you should
create a table, some of whose values are null:

testing=> select f, abstime( f ) from test2;          f|abstime
------------+----------------------------
3.1415926535|Sat Jan 01 02:00:03 2000 IST           |    1.11111|Sat Jan 01 02:00:01 2000 IST
(3 rows)

That's about your testing procedure. About your problem in itself: you
should either use a pl/pgsql procedure or make use of COALESCE.

One last note: EXCEPT is a query operator that excludes certain tuples from
a query. It's not an "else". When you write "SELECT something1 EXCEPT
SELECT something2", it means "select all the 'something1' objects which are
not 'something2'

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma